"""
# pip install pymysql
# pip install pymssql
# pip install DBUtils==1.3  --- 特别要注意
# pip install -i https://pypi.doubanio.com/simple paho-mqtt
"""
import json
import time
import pymysql
import pymssql
#from pymysql.constants import CLIENT
from DBUtils.PooledDB import PooledDB
import os
import requests
from paho.mqtt import client as mqtt_client 

'''
Error Code:
1001 : db code is error.
1002 : db type is error.
1003 : pool params error.
1004 : query faild.
'''
class TPyDB():
    def __init__(self ):
        self.settings = {}
        self.pools ={}

    def setSettings (self, code , dbType , host , port , db , uid , pwd , sqlPath):
        try:
            item = {}
            if code in  self.settings.keys():
                item = self.settings[code]
            else:
                self.settings[code] = item

            item['code'] = code
            item['dbType'] = dbType
            item['host'] = host
            item['port'] = port
            item['db'] = db
            item['uid'] = uid
            item['pwd'] = pwd
            item['sqlPath'] = sqlPath
        except Exception as er:
            print(er)
    def getPool (self, code):
        pool = None
        status = 0
        try:
            if code in self.pools.keys():
                pool = self.pools[code]
                status = 1
            elif code in self.settings.keys():
                settingItem = self.settings[code]
                if settingItem['dbType'] =='mysql':
                    pool , status = self.getMysqlPool(settingItem)
                elif settingItem['dbType'] =='mssql':
                    pool , status  = self.getMssqlPool(settingItem)
                else:
                    status = 1002
            else:
                status = 1001
                print("pool Code is error." , code)
        except Exception as er:
            print(er)
        return  pool , status

    def getMysqlPool(self, settingItem):
        pool = None
        status = 0
        try:
            pool = PooledDB(
                creator=pymysql,
                mincached=1,
                maxcached=100,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit=True,
                client_flag=CLIENT.MULTI_STATEMENTS
            )
            self.pools[settingItem['code']] = pool
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return  pool , status
    def getMssqlPool(self, settingItem):
        pool = None
        status = 0
        try:
            pool = PooledDB(
                creator=pymssql,
                maxconnections=200,
                mincached=10,
                maxcached=15,
                maxshared=0,
                maxusage=1000,
                ping =1,
                blocking=True,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit = True,
                #closeable=False,
            )
            code = settingItem['code']
            self.pools[code] = pool
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return  pool , status
    def getConnection (self, code):
        conn = None
        status = 0
        try:
            pool , poolStatus= self.getPool(code)
            if poolStatus==1 :
                conn = pool.connection()
                status = 1
            else:
                status = poolStatus
        except Exception as er:
            print(er)
        return  conn , status
    def query (self, code  , sql , params):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            conn , connstatus = self.getConnection(code)
            if connstatus == 1 :
                settingItem = self.settings[code]
                dbType = settingItem['dbType']
                cursor = None
                if dbType == 'mysql':
                    cursor = conn.cursor()
                else:
                    cursor = conn.cursor(as_dict=True)
                try:
                    if params != None:
                        cursor.execute(sql, params)
                    else:
                        cursor.execute(sql)
                    if cursor.description != None:
                        rsList = []
                        rs = cursor.fetchall()
                        rsList.append(list(rs))
                        #queryRes['datasetList'].append(list(rs))
                        while cursor.nextset():
                            rs = cursor.fetchall()
                            rsList.append(list(rs))
                            #queryRes['datasetList'].append(list(rs))
                        queryRes['datasetList'] = rsList
                        if len(queryRes['datasetList']) >0 :
                            queryRes['recordset'] = queryRes['datasetList'][0]
                    else:
                        queryRes['executeResult'] ={
                            "rowNum": cursor.rownumber ,
                            "rowCount": cursor.rowcount ,
                            "newId": cursor.lastrowid
                        }
                    queryRes['queryStatus'] = 1
                except Exception as eer:
                    print('query faild.' , eer)
                    queryRes['queryStatus'] = 1004
                    queryRes['errMsg'] = eer;
                cursor.close()
                cursor = None
            else:
                queryRes['queryStatus'] = connstatus
        except Exception as er:
            print(er)
        return  queryRes
    def queryByKey (self, code  , key , params):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                fn = os.path.join(self.settings[code]["sqlPath"], key)
                _, ext = os.path.splitext(fn)
                if ext =='' :
                    fn = fn +'.txt'
                if os.path.exists(fn):
                    sql = ""
                    with open(fn, "r", encoding='utf-8') as f:
                        sql = f.read()
                    queryRes = self.query(code , sql ,  params)
                else:
                    print('file not exists :' , fn)
                    queryRes['queryStatus'] = 1005
                    queryRes['errMsg'] = "key("+key+") is not exists."
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    def createRow (self, code  , table , fieldValues):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                fields = ','.join([x for x  in fieldValues.keys()])
                values = ",".join(["%("+x+")s" for x in fieldValues.keys()])
                sql = "insert into " + table + "(" + fields +") values (" + values+")"
                queryRes = self.query(code , sql , fieldValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    def updateRow (self, code  , table , fieldValues , keyValues):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                fields = ','.join([x+'=%('+x+')s' for x in fieldValues.keys()])
                keys = " and ".join([x+'=%('+x+')s' for x in keyValues.keys()])
                sql = "update "+ table + ' set '+ fields +' where '+ keys
                ps ={}
                for k in fieldValues.keys():
                    ps[k] = fieldValues[k]
                for k in keyValues.keys():
                    ps[k] = keyValues[k]
                queryRes = self.query(code , sql , ps)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    def deleteRow (self, code  , table  , keyValues):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                keys = " and ".join([x+'=%('+x+')s' for x in keyValues.keys()])
                sql = "delete from  "+ table + ' where '+ keys
                queryRes = self.query(code , sql , keyValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes
    def viewRows (self, code  , view  , keyValues):
        queryRes = {
            "queryStatus" :0 ,
            "errMsg":"",
            "executeResult" :{},
            "datasetList":[],
            "recordset":[]
        }
        try:
            if code in self.settings.keys():
                keys = " and ".join([x+'=%('+x+')s' for x in keyValues.keys()])
                sql = "select * from  "+ view + ' where '+ keys
                queryRes = self.query(code , sql , keyValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return  queryRes

class TPyInfluxDB():
    def __init__(self):
        self.settings = {}
        '''
        self.host = ""
        self.port=0
        self.token=""
        self.db = ""
        '''
    def setSettings (self, code ="" ,  host="" , port=0 , db="" , token=""  , sqlPath=""):
        try:
            item = {}
            if code in  self.settings.keys():
                item = self.settings[code]
            else:
                self.settings[code] = item

            item['code'] = code 
            item['host'] = host
            item['port'] = port 
            item['token'] = token
            item['db'] = db
            item['sqlPath'] = sqlPath
        except Exception as er:
            print(er)
    
    def write2TDB(self, tdbCode ,  datapointStr, rp=""):
        res = 0 
        try:            
            if tdbCode in self.settings.keys():
                settings = self.settings[tdbCode]
                host = settings['host']
                port = settings['port']
                token = settings['token']
                db = settings['db']
                headers = {
                    "Authorization": token,
                    'Content-Type': 'application/json',
                }
                influxdb_url = "http://" + host + ":" + str(port) + '/write?db=' + db
                if rp != '':
                    influxdb_url = influxdb_url + '&rp=rePolicy_1h'
                resp = requests.post(url=influxdb_url, headers=headers, data=datapointStr)
                #print(resp)
                res = 1 
            else:
                res = 1001
            #print('save to tdb:', resp)
        except Exception as er:
            print(er)
        return res
class TMq :
    def __init__(self):
        self.host = "127.0.0.1"
        self.port = 31274
        self.uid = "admin"
        self.pwd = "hhuc1115"
        self.clientId = ""
        self.client = None
        self.subscribeTopics=[]
        self.actived = False
        self.events ={
            "onConnect": self.eventOnConnect,               #eventOnConnect(client, userdata, flags, rc)
            "onDisConnect": self.eventOnDisconnect,         #eventOnDisConnect(client, userdata, rc)
            "onMessage": self.eventOnMessage,               #eventOnMessage(client, userdata, topic, payload,  message)
        }

    def setSettings(self , host="" , port=0 , uid="" , pwd="" , subscribeTopics=[]): 
        try:
            self.host = host
            self.port = port
            self.uid = uid
            self.pwd = pwd
            if len(subscribeTopics)>0:
                self.subscribeTopics = subscribeTopics
        except Exception as er:
            res = False
            print(er) 
    def isConnected(self):
        res = False
        try:
            res = self.client!=None and self.actived
        except Exception as er:
            res = False
            print(er)
        return res
    def connect(self):
        try:
            userData ={"o:""mqc-2024"}
            clientId = "mqc"+str(time.time())
            if self.client ==None:
                self.client =  mqtt_client.Client( client_id=clientId, clean_session=True, userdata=userData, transport="tcp")
            else:
                self.client.reinitialise(clientId, clean_session=True , userdata=userData)
            self.client.username_pw_set( username=self.uid , password= self.pwd)
            self.client.on_connect = self.on_connect
            self.client.on_disconnect = self.on_disconnect
            self.client.on_message = self.on_message
            self.client.connect(host=self.host , port=self.port , keepalive=60 )
            self.actived = True
        except Exception as er :
            print(er)
        res = self.isConnected()
        return  res
    def start(self, asyncFlag = False):
        try:
            self.connect()
            time.sleep(1.0)
            flag = self.isConnected()
            if flag:
                if asyncFlag :
                    self.client.loop_start()
                else:
                    self.client.loop_forever()
        except Exception as er :
            print(er)
        res = self.isConnected()
        return  res
    def disConnect(self):
        try:
            print('dis')
        except Exception as er :
            print(er)
    def sendMsg(self, topic , messagePayload):
        try:
            flag = self.isConnected()
            if flag :
                self.client.publish(topic=topic, payload=messagePayload, retain=False)
        except Exception as er :
            print(er)
    def subscribe(self, topics):
        try:
            self.client.subscribe(topics)
        except Exception as er :
            print(er)
    def on_connect(self , client, userdata, flags, rc):
        try:
            print('connect to mqtt broker', "flags=", flags, "rc=", rc)
            if rc==0 :
                self.actived = True
                if len( self.subscribeTopics)>0:
                    for topic in self.subscribeTopics:
                        self.client.subscribe(topic)
            else:
                self.actived = False
            if "onConnect" in self.events.keys() and  self.events["onConnect"]!=None:
                fun = self.events["onConnect"]
                fun(client,userdata, flags, rc)
        except Exception as er :
            print(er)
    def on_disconnect(self , client, userdata, rc):
        try:
            print('disconnect from mqtt broker', "rc=", rc)
            self.actived = False
            if "onDisConnect" in self.events.keys() and  self.events["onDisConnect"]!=None:
                fun = self.events["onDisConnect"]
                fun(client,userdata, rc)
        except Exception as er :
            print(er)
    def on_message(self , client, userdata, message):
        try:
            print('rec message from  mqtt broker', message)
            print('topic=', message.topic)
            print('payload=', str(message.payload) )
            if "onMessage" in self.events.keys() and  self.events["onMessage"]!=None:
                fun = self.events["onMessage"]
                fun(client,userdata, message.topic , str(message.payload) , message)
        except Exception as er :
            print(er)
    def eventOnConnect(self , client, userdata, flags, rc):
        try:
            pass
        except Exception as er :
            print(er)
    def eventOnDisconnect(self , client, userdata, rc):
        try:
            pass
        except Exception as er :
            print(er)
    def eventOnMessage(self , client, userdata, topic, payload,  message):
        try:
            pass
        except Exception as er :
            print(er)



